USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetMyTasksThisWeek')
	BEGIN
		DROP  Procedure  dbo.GetMyTasksThisWeek
	END

GO

CREATE Procedure dbo.GetMyTasksThisWeek
	@userId INT
AS
BEGIN

	;WITH A AS (
		SELECT 
			a.ActivityTaskID	AS TaskID
		,	a.ActivityTaskGUID	AS PKID
		,	a.[Description]		AS TaskName
		,	a.StartDate			as StartDate
		,	a.EndDate			as EndDate
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = 3 -- OWNER
		AND ISNULL(a.EndDate, GetDate()) BETWEEN (GetDate() - 7) AND (GetDate()+7)
		AND ISNULL(IsTask,0) = 1
	), B AS (
		-- GET ACCOUNTS FOR THE ACTIVITIES
		SELECT
			act.PartnerName		as 	AccountName
		,	a.TaskID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as act WITH (NOLOCK)
			on act.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 10 -- 10: Task Partner
	), C AS (
		-- GET CONTACT FOR THE TASK
		SELECT
			ISNULL(con.PartnerFirstName,'') + ' ' + ISNULL(con.PartnerLastName,'')		as 	Contact
		,	a.TaskID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as con WITH (NOLOCK)
			on con.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 2 -- Contact
	), Final AS (
		SELECT
			b.AccountName		AS [Company Name]
		,	a.TaskName			AS [Task Name]
		,	c.Contact
		,	CONVERT(VARCHAR(10), a.EndDate, 101)	as [Due Date]
		,	a.PKID				AS [PKID]
		FROM A
		LEFT OUTER JOIN B
			on B.TaskID = A.TaskID
		LEFT OUTER JOIN C
			on C.TaskID = A.TaskID
	)

	SELECT * FROM Final
END
GO



-- exec dbo.GetMyTasksThisWeek 2